<?php 

require("../include/config.php");
require("../API/php/fonctions.php");
require("../API/php/header.php");

// phpsql/mysql_getEcartTypeParDom.php?milis=123450&dom=PNM

//[x][0] = moy
//[x][1] = varian
//[x][2] = ecart_type
//[x][3] = bollinger_minus
//[x][4] = bollinger_max
//[x][5] = domaine_fonctionnel

// IN obligatoire
$arrayGet = array(
    "dom" => null
);

$arrayValeur = recupGet($arrayGet, $bolDecode);

if($arrayValeur["error"] == null){
	// CONNECION SQL
	$db = mysql_connect($host, $base, $mdp)  or die('Erreur de connexion '.mysql_error());

	// CONNECION BASE
	mysql_select_db($base,$db)  or die('Erreur de selection '.mysql_error());

	//------------------------------------------------------------------
	
	if($arrayValeur["dom"] != "all"){
		$filtreDom = "AND d.`domaine_fonctionnel` like '%".$arrayValeur["dom"]."%'";
	}else{
		$filtreDom = "";
	}
	
	//Les moyennes de dispertion
	$sql = "SELECT e.`domaine_fonctionnel`, e.moy, e.varian, e.ecart_type, ROUND(e.moy - e.ecart_type) as bollinger_minus, ROUND(e.moy + e.ecart_type) as bollinger_max
		FROM (
			SELECT c.`domaine_fonctionnel`, ROUND(AVG(c.nombre),2) as moy, ROUND(VARIANCE(c.nombre),2) as varian,
			ROUND(SQRT(VARIANCE(c.nombre)),2) as ecart_type
			FROM (
				SELECT d.`domaine_fonctionnel`, DATE_FORMAT(a.date_creation,'%Y-%m-%d') as date_creation, count(*) as nombre 
				FROM `vm_inventaire_tocs` a, affectation b, vue_ticket d
				WHERE 1=1
				AND a.id_toc_ori = d.id_toc
				AND DATE_FORMAT(a.date_creation,'%Y-%m-%d') = b.date
				".$filtreDom."
				AND DATE_FORMAT(a.date_creation,'%Y-%m') = DATE_FORMAT((NOW() - INTERVAL 1 MONTH),'%Y-%m')
				AND b.open != b.close
				GROUP BY DATE_FORMAT(a.date_creation,'%Y-%m-%d'), d.`domaine_fonctionnel`
				ORDER BY date_creation
			) c
			GROUP BY c.`domaine_fonctionnel`
		) e
	;";

	$req = mysql_query($sql) or die('Erreur SQL !<br>'.$sql.'<br>'.mysql_error()); 

	// on fait une boucle qui va faire un tour pour chaque enregistrement 
	while($data = mysql_fetch_assoc($req)) 
	{ 
		$strSorti .= $data['moy'].'&'.$data['varian'].'&'.$data['ecart_type'].'&'.$data['bollinger_minus'].'&'.$data['bollinger_max'].'&'.$data['domaine_fonctionnel']
		.'&'
		."\r\n"; 
	}

	// DECONNECION SQL
	mysql_close();
}else{
	$strSorti = "ERROR:".$arrayValeur["error"];
}

require("../API/php/footer.php");
?>